<?php
function getVideoCategories()
{
	$select = 'SELECT id, name_'.LANG.' FROM '.DB_PERFIX.'video_cats;';
	$result = mysql_query($select);

	if(mysql_num_rows($result) > 0)
	{
		$i = 0;
		while($row = mysql_fetch_array($result))
		{
			$item[$i]['id'] = $row['id'];
			$item[$i]['name'] = $row['name_'.LANG.''];
			$i++;
		}
		return $item;
	}
}
function storeUploadedVideo($userid, $cat, $title, $description, $tags, $privacy, $filename)
{	
	$title = strip_tags(trim($title));
	$description = strip_tags(trim($description));
	$tags = strip_tags(trim($tags));
	
	$insert  = 'INSERT INTO '.DB_PERFIX.'video_clips (`id`, `user_id`, `cat_id`, `name`, `description`, `keywords`, `level`, `file`, `added`)';
	$insert .= 'VALUES (NULL, '.$userid.', '.$cat.', "'.$title.'", "'.$description.'", "'.$tags.'", '.$privacy.', "'.$filename.'", NOW());';
	$result = mysql_query($insert);
	if($result > 0)
	{ 
		$select = 'SELECT id FROM '.DB_PERFIX.'video_clips WHERE user_id='.$userid.' AND cat_id='.$cat.' ORDER BY id DESC LIMIT 1;';
		$result = mysql_query($select);
		if(mysql_num_rows($result) > 0)
		{
			while($row = mysql_fetch_array($result))
			{
				$item = $row['id'];
			}
		}
		return 'You have sucessfully uploaded your video clip. <br />Click <a href="?c=14&item='.$item.'" style="color:black;">HERE</a> to view it.'; 
	}
	else
	{ return; }
}

function getVideoAll($id)
{
	if($id > 0)
	{
		$selectString = 'v.user_id, u.username, v.id, v.cat_id, v.views, vc.name_'.LANG.', v.name, v.keywords, v.description, v.file, v.length, v.added, vr.stars, vr.votes';
		$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'video_cats AS vc ON vc.id=v.cat_id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'video_rating AS vr ON vr.video_id=v.id ';
		$select = $select.' WHERE v.id='.$id.' LIMIT 1;';
		
		$result = mysql_query($select);
		if(mysql_num_rows($result) > 0)
		{
			while($row = mysql_fetch_array($result))
			{
				$item['id'] = $row['id'];
				$item['cat_id'] = $row['cat_id'];
				$item['cat_name'] = $row['name_'.LANG];
				$item['user_id'] = $row['user_id'];
				$item['username'] = $row['username'];
				$item['name'] = $row['name'];
				$item['keywords'] = $row['keywords'];
				$item['description'] = $row['description'];
				$item['file'] = $row['file'];
				$item['length'] = $row['length'];
				$item['views'] = $row['views'];
				$item['add_day'] = substr($row['added'],8,2);
				$item['add_month'] = substr($row['added'],5,2);
				$item['add_year'] = substr($row['added'],0,4);
				$item['added'] = $item['add_day'].'.'.$item['add_month'].'.'.$item['add_year'];
				
				$item['stars'] = $row['stars'];
				$item['votes'] = $row['votes'];
				if(($item['stars'] > 0) && ($item['votes'] > 0))
				{ $item['avg'] = round(($item['stars']/$item['votes']),2); }
				else
				{ $item['avg'] = 0; }
			}
			if($item['id'] > 0)
			{ mysql_query('UPDATE '.DB_PERFIX.'video_clips SET views=views+1 WHERE id='.$item['id'].';'); }
			return $item;
		}
	}
}

function getVideos($type, $limit=20)
{
	switch ($type)
	{
		case 'recent':
			$whereString = '';
			$orderString = 'ORDER BY id DESC';
			break;  
		case 'random':
			$whereString = '';
			$orderString = 'ORDER BY NULL';
			break;  
		case 'all':
			$whereString = '';
			$orderString = 'ORDER BY v.name ASC';
			break;
		default:
			$whereString = '';
			$orderString = 'ORDER BY name ASC';
	}
	if($limit > 0)
	{ $limitstr=' LIMIT '.$limit; }
	$selectString = 'v.user_id, u.username, v.id, v.cat_id, v.views, vc.name_'.LANG.', v.name, v.keywords, v.description, v.file, v.length';
	
	$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'video_cats AS vc ON vc.id=v.cat_id ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
	$select = $select.$whereString.$orderString.' '.$limitstr.';';
	$result = mysql_query($select);
	if(mysql_num_rows($result) > 0)
	{
		$i = 0;
		while($row = mysql_fetch_array($result))
		{
			$item[$i]['id'] = $row['id'];
			$item[$i]['cat_id'] = $row['cat_id'];
			$item[$i]['cat_name'] = $row['name_'.LANG];
			$item[$i]['user_id'] = $row['user_id'];
			$item[$i]['username'] = $row['username'];
			$item[$i]['name'] = $row['name'];
			$item[$i]['keywords'] = $row['keywords'];
			$item[$i]['description'] = $row['description'];
			$item[$i]['file'] = $row['file'];
			$item[$i]['length'] = $row['length'];
			$item[$i]['views'] = $row['views'];
			$i++;
		}
		return $item;
	}
}
function getVideosAdv($type, $limit=20)
{
	switch ($type)
	{
		case 'recent':
			$whereString = '';
			$orderString = 'ORDER BY id DESC';
			break;  
		case 'random':
			$whereString = '';
			$orderString = 'ORDER BY NULL';
			break;  
		default:
			$whereString = '';
			$orderString = 'ORDER BY name ASC';
	}
	$selectString = 'v.user_id, u.username, v.id, v.cat_id, v.views, vc.name_'.LANG.', v.name, v.keywords, v.description, v.file, v.length, vr.stars, vr.votes';
	
	$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'video_cats AS vc ON vc.id=v.cat_id ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'video_rating AS vr ON vr.video_id=v.id ';
	$select = $select.$whereString.$orderString.' LIMIT '.$limit.';';
	
	$result = mysql_query($select);
	if(mysql_num_rows($result) > 0)
	{
		$i = 0;
		while($row = mysql_fetch_array($result))
		{
			$item[$i]['id'] = $row['id'];
			$item[$i]['cat_id'] = $row['cat_id'];
			$item[$i]['cat_name'] = $row['name_'.LANG];
			$item[$i]['user_id'] = $row['user_id'];
			$item[$i]['username'] = $row['username'];
			$item[$i]['name'] = $row['name'];
			$item[$i]['keywords'] = $row['keywords'];
			$item[$i]['description'] = $row['description'];
			$item[$i]['file'] = $row['file'];
			$item[$i]['length'] = $row['length'];
			$item[$i]['views'] = $row['views'];
			$item[$i]['stars'] = $row['stars'];
			$item[$i]['votes'] = $row['votes'];
			if(($item[$i]['stars'] > 0) && ($item[$i]['votes'] > 0))
			{ $item[$i]['avg'] = round(($item[$i]['stars']/$item[$i]['votes']),2); }
			else
			{ $item[$i]['avg'] = 0; }
			$i++;
		}
		return $item;
	}
}
function getMemberVideos($userid, $category, $order, $adsc='DESC', $limit=20)
{
	if($userid > 0)
	{
		$whereString = ' WHERE u.id='.$userid;
		switch ($order)
		{
			case 'date':
				$orderString = ' ORDER BY v.id '.$adsc;
				break;  
			case 'random':
				$orderString = ' ORDER BY NULL';
				break;  
			default:
				$orderString = ' ORDER BY v.id '.$adsc;
		}
		if($limit == 0)
		{ $limit = ''; }
		else
		{ $limit =  ' LIMIT '.$limit; }
		
		if($category > 0)
		{ $whereString = $whereString.' AND v.cat_id='.$category; }
		
		$selectString = 'v.user_id, u.username, v.id, v.cat_id, v.views, vc.name_'.LANG.', v.name, v.keywords, v.description, v.file, v.length, vr.stars, vr.votes';
		
		$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'video_cats AS vc ON vc.id=v.cat_id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'video_rating AS vr ON vr.video_id=v.id ';
		$select = $select.$whereString.$orderString.$limit.';';
		
		$result = mysql_query($select);
		if(mysql_num_rows($result) > 0)
		{
			$i = 0;
			while($row = mysql_fetch_array($result))
			{
				$item[$i]['id'] = $row['id'];
				$item[$i]['cat_id'] = $row['cat_id'];
				$item[$i]['cat_name'] = $row['name_'.LANG];
				$item[$i]['user_id'] = $row['user_id'];
				$item[$i]['username'] = $row['username'];
				$item[$i]['name'] = $row['name'];
				$item[$i]['keywords'] = $row['keywords'];
				$item[$i]['description'] = $row['description'];
				$item[$i]['file'] = $row['file'];
				$item[$i]['length'] = $row['length'];
				$item[$i]['views'] = $row['views'];
				$item[$i]['stars'] = $row['stars'];
				$item[$i]['votes'] = $row['votes'];
				if(($item[$i]['stars'] > 0) && ($item[$i]['votes'] > 0))
				{ $item[$i]['avg'] = round(($item[$i]['stars']/$item[$i]['votes']),2); }
				else
				{ $item[$i]['avg'] = 0; }
				$i++;
			}
			return $item;
		}
	}
}
function getMemberVideosCount($userid)
{
	if($userid > 0)
	{
		$whereString = ' WHERE user_id='.$userid;
		$selectString = 'COUNT(id)';
		
		$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips ';
		$select = $select.$whereString.';';
		
		$result = mysql_query($select);
		if(mysql_num_rows($result) > 0)
		{
			while($row = mysql_fetch_array($result))
			{
				$item = $row['COUNT(id)'];
			}
			return $item;
		}
	}
}
function getFeaturedVideo($location, $limit=1)
{
	if($location > 0)
	{
		$whereString = ' WHERE ft.type=1 AND ft.location='.$location;
		
		$selectString = 'v.user_id, u.username, v.id, v.cat_id, v.views, vc.name_'.LANG.', v.name, v.keywords, v.description, v.file, v.length, vr.stars, vr.votes';
		
		$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'video_cats AS vc ON vc.id=v.cat_id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'video_rating AS vr ON vr.video_id=v.id ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'featured AS ft ON ft.featured_id=v.id ';
		$select = $select.$whereString.' ORDER BY NULL LIMIT '.$limit.';';
		
		$result = mysql_query($select);
		if(mysql_num_rows($result) > 0)
		{
			$i = 0;
			while($row = mysql_fetch_array($result))
			{
				$item[$i]['id'] = $row['id'];
				$item[$i]['cat_id'] = $row['cat_id'];
				$item[$i]['cat_name'] = $row['name_'.LANG];
				$item[$i]['user_id'] = $row['user_id'];
				$item[$i]['username'] = $row['username'];
				$item[$i]['name'] = $row['name'];
				$item[$i]['keywords'] = $row['keywords'];
				$item[$i]['description'] = $row['description'];
				$item[$i]['file'] = $row['file'];
				$item[$i]['length'] = $row['length'];
				$item[$i]['views'] = $row['views'];
				$item[$i]['stars'] = $row['stars'];
				$item[$i]['votes'] = $row['votes'];
				if(($item[$i]['stars'] > 0) && ($item[$i]['votes'] > 0))
				{ $item[$i]['avg'] = round(($item[$i]['stars']/$item[$i]['votes']),2); }
				else
				{ $item[$i]['avg'] = 0; }
				$i++;
			}
			return $item;
		}
	}
}

function searchVideosAll($keywords='', $cat_id=0, $order=0, $limit=20)
{
	$whereString = 'WHERE v.id > 0 ';
	if($keywords != '')
	{
		$keywords	  = strip_tags(trim($keywords));
		$whereString .= ' AND (((v.description LIKE ("'.$keywords.'%"))) OR ((v.name LIKE ("'.$keywords.'%"))) OR (v.keywords LIKE ("'.$keywords.'%")) OR (vc.name_'.LANG.' LIKE ("'.$keywords.'%"))) ';
	}
	if($cat_id > 0)
	{ $whereString .= ' AND v.cat_id = '.$cat_id.' '; }
	
	switch ($order)
	{
		case 0:
			$orderString = 'ORDER BY v.id DESC ';
			break;  
		case 1:
			$orderString = 'ORDER BY v.views DESC ';
			break; 
		case 2:
			$orderString = 'ORDER BY v.id DESC ';
			break; 
	}
	
	if($limit == 0)
	{ $limit = ''; }
	else
	{
		if($limit == "")
		{ $limit = ' LIMIT 20'; }
		else
		{ $limit = ' LIMIT '.$limit; }
	}
	
	$selectString = 'v.user_id, u.username, v.id, v.cat_id, v.views, vc.name_'.LANG.', v.name, v.keywords, v.description, v.file, v.length';
	
	$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'video_cats AS vc ON vc.id=v.cat_id ';
	$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
	$select = $select.$whereString.$orderString.$limit.';';
	
	$result = mysql_query($select);
	if(mysql_num_rows($result) > 0)
	{
		$i = 0;
		while($row = mysql_fetch_array($result))
		{
			$item[$i]['id'] = $row['id'];
			$item[$i]['cat_id'] = $row['cat_id'];
			$item[$i]['cat_name'] = $row['name_'.LANG];
			$item[$i]['user_id'] = $row['user_id'];
			$item[$i]['username'] = $row['username'];
			$item[$i]['name'] = $row['name'];
			$item[$i]['keywords'] = $row['keywords'];
			$item[$i]['description'] = $row['description'];
			$item[$i]['file'] = $row['file'];
			$item[$i]['length'] = $row['length'];
			$item[$i]['views'] = $row['views'];
			$i++;
		}
		return $item;
	}
}
function displayFlashVideo($clip_id, $type, $file, $width, $height)
{
	if($type == 'flvplayer'):
		$output  = '<div class="v_player" id="player'.$clip_id.'"></div> ';
		$output .= '<script type="text/javascript"> ';
		$output .= 'var v'.$clip_id.' = new SWFObject("'.SITE_URL.'players/video/flvplayer.swf","single","'.$width.'","'.$height.'","7"); ';
		$output .= 'v'.$clip_id.'.addParam("allowfullscreen","true"); ';
		$output .= 'v'.$clip_id.'.addVariable("file","'.SITE_URL.'upload/video/flv/'.$file.'"); ';
		$output .= 'v'.$clip_id.'.addVariable("image","'.SITE_URL.'upload/video/thumbs/'.substr($file,0,-4).'.jpg"); ';
		$output .= 'v'.$clip_id.'.addVariable("displayheight","'.($height-20).'"); ';
		$output .= 'v'.$clip_id.'.addVariable("backcolor","0xffffff"); ';
		$output .= 'v'.$clip_id.'.addVariable("frontcolor","0x4A4A4A"); ';
		$output .= 'v'.$clip_id.'.addVariable("lightcolor","0x000000"); ';
		$output .= 'v'.$clip_id.'.addVariable("width","'.$width.'"); ';
		$output .= 'v'.$clip_id.'.addVariable("height","'.$height.'"); ';
		$output .= 'v'.$clip_id.'.write("player'.$clip_id.'"); ';
		$output .= '</script>';
	endif;
	return $output;
}
function createVideoRating($id)
{
	$insert  = 'INSERT INTO '.DB_PERFIX.'video_rating (`video_id`)'; 
	$insert .= 'VALUES ('.$id.');';
	$result = mysql_query($insert);
	return $result;
}
function postVideoVote($voterid, $videoid, $stars)
{
	$select = 'SELECT video_id FROM '.DB_PERFIX.'video_rating WHERE video_id='.$videoid.' LIMIT 1;';
	$result = mysql_query($select);
	if(!($result[0]['user_id'] > 0))
	{
		createVideoRating($videoid);
	}
	
	$select = 'SELECT video_id, voter_id FROM '.DB_PERFIX.'video_votes WHERE video_id='.$videoid.' AND voter_id='.$voterid.' LIMIT 1;';
	$result = mysql_query($select);
	if(mysql_num_rows($result) > 0)
	{
		$update = 'UPDATE '.DB_PERFIX.'video_votes SET stars='.$stars.' WHERE video_id='.$videoid.' AND voter_id='.$voterid.' LIMIT 1;';
		$result = mysql_query($update);
		return $result;
	}
	else
	{
		$insert  = 'INSERT INTO '.DB_PERFIX.'video_votes (`video_id` , `voter_id` , `stars`)'; 
		$insert .= 'VALUES ('.$videoid.' , '.$voterid.' , '.$stars.');';
		$result = mysql_query($insert);
		return $result;
	}
}

function calculateVideoRating($videoid)
{
	$select = 'SELECT video_id FROM '.DB_PERFIX.'video_rating WHERE video_id='.$videoid.';';
	$result = mysql_query($select);
	$i=0;
	while($row = mysql_fetch_array($result))
	{
		$items[$i]['id'] = $row['video_id'];
		$i++;
	}
	foreach($items as $item)
	{
		$select = 'SELECT COUNT(id) , SUM(stars) FROM '.DB_PERFIX.'video_votes WHERE video_id='.$item['id'].';';
		$result = mysql_query($select);
		$j=0;
		while($row = mysql_fetch_array($result))
		{
			$votes[$j] = $row['COUNT(id)'];
			$stars[$j] = $row['SUM(stars)'];
			$update = 'UPDATE '.DB_PERFIX.'video_rating SET stars='.$stars[$j].', votes='.$votes[$j].' WHERE video_id="'.$item['id'].'" LIMIT 1;';
			$updated = mysql_query($update);
		}
	}
}

function calculateVideoRatingAll()
{
	$select = 'SELECT video_id FROM '.DB_PERFIX.'video_rating;';
	$result = mysql_query($select);
	$i=0;
	while($row = mysql_fetch_array($result))
	{
		$items[$i]['id'] = $row['video_id'];
		$i++;
	}
	foreach($items as $item)
	{
		$select = 'SELECT COUNT(id) , SUM(stars) FROM '.DB_PERFIX.'video_votes WHERE video_id='.$item['id'].';';
		$result = mysql_query($select);
		$j=0;
		while($row = mysql_fetch_array($result))
		{
			$votes[$j] = $row['COUNT(id)'];
			$stars[$j] = $row['SUM(stars)'];
			$update = 'UPDATE '.DB_PERFIX.'video_rating SET stars='.$stars[$j].', votes='.$votes[$j].' WHERE video_id="'.$item['id'].'" LIMIT 1;';
			$updated = mysql_query($update);
		}
	}
}

function getVideoComments($videoid, $limit="", $order)
{
	if($limit != "")
	{ $limit = ' LIMIT '.$limit; }
	
	$select = 'SELECT vc.id, vc.comment, vc.embed, vc.posted, vc.from_id, vc.to_id, u.username, u.image FROM '.DB_PERFIX.'video_comments AS vc, '.DB_PERFIX.'users AS u WHERE vc.to_id='.$videoid.' AND vc.from_id=u.id ORDER BY vc.posted '.$order.' '.$limit.';';
	$result = mysql_query($select);
	if(mysql_num_rows($result) > 0)
	{
		$i = 0;
		while($row = mysql_fetch_array($result))
		{
			$item[$i]['id'] = $row['id'];
			$item[$i]['from_id'] = $row['from_id'];
			$item[$i]['to_id'] = $row['to_id'];
			$item[$i]['username'] = $row['username'];
			$item[$i]['image'] = $row['image'];
			$item[$i]['comment'] = $row['comment'];
			$item[$i]['embed'] = $row['embed'];
			$item[$i]['posted'] = $row['posted'];
			$i++;
		}
		return $item;
	}
}
function postVideoComment($from_id, $to_id, $comment)
{
	$comment = stripslashes(strip_tags(trim($comment)));
	if(($from_id > 0) && ($to_id > 0) && ($comment != ''))
	{
		$insert = 'INSERT INTO '.DB_PERFIX.'video_comments (`id`, `from_id`, `to_id`, `comment`, `posted`)';
		$insert .= 'VALUES (NULL, '.$from_id.', '.$to_id.', "'.$comment.'", NOW());';
		$result = mysql_query($insert);
		return $result;
	}
}

function deleteMemberVideos($userid)
{
	if($userid > 0)
	{
		$whereString = ' WHERE u.id='.$userid;
		
		$selectString = 'v.id';
		
		$select = ' SELECT '.$selectString.' FROM '.DB_PERFIX.'video_clips AS v ';
		$select = $select.' LEFT JOIN '.DB_PERFIX.'users AS u ON u.id=v.user_id ';
		$select = $select.$whereString.';';
		
		$result = mysql_query($select);
		if(mysql_num_rows($result) > 0)
		{
			while($row = mysql_fetch_array($result))
			{ 
				delVideo($row['id'], $userid);
			}
			return;
		}
	}
}

function delVideo($id, $userid)
{
	$select = 'SELECT id FROM '.DB_PERFIX.'video_clips WHERE id='.$id.' AND user_id='.$userid.' LIMIT 1;';
	$result = mysql_query($select);
	if(mysql_num_rows($result) > 0)
	{
		$delete = 'DELETE FROM '.DB_PERFIX.'video_comments WHERE to_id='.$id.';';
		$result = mysql_query($delete);
		$delete = 'DELETE FROM '.DB_PERFIX.'video_votes WHERE video_id='.$id.';';
		$result = mysql_query($delete);
		$delete = 'DELETE FROM '.DB_PERFIX.'video_rating WHERE video_id='.$id.';';
		$result = mysql_query($delete);
		$delete = 'DELETE FROM '.DB_PERFIX.'video_clips WHERE id='.$id.' AND user_id='.$userid.' LIMIT 1;';
		$result = mysql_query($delete);
	}
}

function delVideoComment($comid, $userid)
{
	$delete = 'DELETE FROM '.DB_PERFIX.'video_comments WHERE id='.$comid.' AND (from_id='.$userid.' OR to_id='.$userid.');';
	$result = mysql_query($delete);
}

function makeMultipleTwo ($value)
{
	$sType = $value%2;
	if($sType == 0)
	{ return $value; } 
	else 
	{ return ($value-1); }
}
?>